﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SQLite;
using System.IO;
using System.Text;

namespace HAOest.Data.SQLite
{
    /// <summary>
    /// SQLite工具类
    /// <para>作者：邓斌HADB</para>
    /// <!--修订：2012-09-29 V1.1.25.0建立-->
    /// </summary>
    public class SQLite
    {
        private SQLiteConnection sqliteConnection = new SQLiteConnection();
        private SQLiteCommand sqliteCommand = new SQLiteCommand();
        private DbTransaction dbTransaction = null;
        private bool transactionOn = false; //是否开始事务模式

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="databaseFileName">数据库文件名</param>
        /// <param name="password">可选密码</param>
        public SQLite(string databaseFileName, string password = null)
        {
            SQLiteFunction.RegisterFunction(typeof(SQLiteCollationPinYin));//注册拼音排序功能

            if (!File.Exists(databaseFileName)) SQLiteConnection.CreateFile(databaseFileName);//如果数据库不存在则创建数据库文件

            SQLiteConnectionStringBuilder connStrBuilder = new SQLiteConnectionStringBuilder();
            connStrBuilder.DataSource = databaseFileName;
            if (password != null) connStrBuilder.Password = password;

            sqliteConnection.ConnectionString = connStrBuilder.ToString();

            try
            {
                sqliteConnection.Open();
            }
            catch
            {
                throw new ApplicationException("HAOest.Data.SQLite：数据库打开失败");
            }

            sqliteCommand.Connection = sqliteConnection;
        }

        /// <summary>
        /// 创建表（如果不存在的话）
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="primaryKeyName">主键名</param>
        /// <param name="autoIncrement">主键是否为自Integer型</param>
        /// <param name="columnNames">其余所有列名</param>
        public void CreateTableIfNotExists(string tableName, string primaryKeyName, bool autoIncrement, params string[] columnNames)
        {
            if (columnNames == null)
            {
                throw new ArgumentException("HAOest.Data.SQLite.CreateTableIfNotExists：参数列表为null");
            }

            if (columnNames.Length == 0)
            {
                throw new ArgumentException("HAOest.Data.SQLite.CreateTableIfNotExists：空白参数列表");
            }

            StringBuilder commandText = new StringBuilder();
            commandText.Append(string.Format("CREATE TABLE IF NOT EXISTS {0} ( ", tableName));

            if (autoIncrement)
            {
                commandText.Append(string.Format("{0} INTEGER PRIMARY KEY AUTOINCREMENT ", primaryKeyName));
            }
            else
            {
                commandText.Append(string.Format("{0} TEXT PRIMARY KEY ", primaryKeyName));
            }

            foreach (string columnName in columnNames)
            {
                commandText.Append(string.Format(", {0} TEXT", columnName));
            }
            commandText.Append(")");

            ExecuteNonQuery(commandText.ToString());
        }

        /// <summary>
        /// 插入
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnNames">所有列名</param>
        /// <param name="values">所有对应的值</param>
        public void Insert(string tableName, List<string> columnNames, List<string> values)
        {
            for (int i = 0; i < values.Count; i++)
            {
                values[i] = "'" + values[i] + "'";
            }
            string columnNamesString = string.Join(", ", columnNames.ToArray());
            string valuesString = string.Join(", ", values.ToArray());
            StringBuilder commandText = new StringBuilder();
            commandText.Append("INSERT OR REPLACE INTO ").Append(tableName).Append("(").Append(columnNamesString).Append(") VALUES(").Append(valuesString).Append(")");

            ExecuteNonQuery(commandText.ToString());
        }

        /// <summary>
        /// 更新
        /// </summary>
        /// <param name="tableName"></param>
        /// <param name="columnNames"></param>
        /// <param name="values"></param>
        /// <param name="condition">条件（不含"WHERE"），可为空</param>
        public void Update(string tableName, List<string> columnNames, List<string> values, string condition = null)
        {
            StringBuilder commandText = new StringBuilder();

            commandText.Append(string.Format("UPDATE {0} SET ", tableName));
            for (int i = 0; i < columnNames.Count - 1; i++)
            {
                commandText.Append(string.Format("{0} = '{1}', ", columnNames[i], values[i]));
            }
            if (condition != null)
            {
                commandText.Append(string.Format("{0} = '{1}' WHERE {2}", columnNames[columnNames.Count - 1], values[values.Count - 1], condition));
            }
            else
            {
                commandText.Append(string.Format("{0} = '{1}'", columnNames[columnNames.Count - 1], values[values.Count - 1]));
            }

            ExecuteNonQuery(commandText.ToString());

        }

        /// <summary>
        /// 删除
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="condition">条件（不含“WHERE”）</param>
        public void Delete(string tableName, string condition)
        {
            ExecuteNonQuery(string.Format("DELETE FROM {0} WHERE {1}", tableName, condition));
        }

        /// <summary>
        /// 查询单个字段的值
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnName">该字段所在列名</param>
        /// <param name="condition">条件</param>
        /// <returns></returns>
        public string Select(string tableName, string columnName, string condition)
        {
            StringBuilder commandText = new StringBuilder();
            commandText.Append(string.Format("SELECT {0} FROM {1} WHERE {2}", columnName, tableName, condition));

            SQLiteCommand cmd = new SQLiteCommand();
            cmd.Connection = sqliteConnection;
            cmd.CommandText = commandText.ToString();
            SQLiteDataReader dataReader = cmd.ExecuteReader();
            if (dataReader.Read())
            {
                string result = Convert.ToString(dataReader.GetValue(0));
                dataReader.Close();
                return result;
            }
            else
            {
                dataReader.Close();
                throw new ApplicationException("HAOest.Data.SQLite.Select：读取不到值");
            }
        }

        /// <summary>
        /// 判断表是否存在
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <returns>存在返回true；不存在返回false</returns>
        public bool TableExists(string tableName)
        {
            sqliteCommand.CommandText = string.Format("SELECT COUNT(*) FROM sqlite_master WHERE TYPE = 'table' AND NAME='{0}'", tableName);
            if (Convert.ToInt16(sqliteCommand.ExecuteScalar()) == 0)
            {
                return false;
            }
            else
            {
                return true;
            }
        }

        /// <summary>
        /// 获取DataTable
        /// </summary>
        /// <param name="sql">查询的sql语句（若要以拼音排序中文，则加一下在"ORDER BY [ColumnName]" 后面加上 "COLLATE PinYin"）</param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql)
        {
            sqliteCommand.CommandText = sql;
            SQLiteDataAdapter sqliteDataAdapter = new SQLiteDataAdapter(sqliteCommand);
            DataTable dataTable = new DataTable();
            try
            {
                sqliteDataAdapter.Fill(dataTable);
            }
            catch (SQLiteException sqlEx)
            {
                throw new SQLiteException("HAOest.Data.SQLite.Select：读取DataTable出错\nsql语句为：" + sql + "\n错误原因：" + sqlEx.Message, sqlEx);
            }
            return dataTable;
        }

        /// <summary>
        /// 获取DataRow
        /// </summary>
        /// <param name="sql">查询的sql语句</param>
        /// <returns></returns>
        public DataRow GetDataRow(string sql)
        {
            DataTable dataTable = GetDataTable(sql);
            return dataTable.Rows[0];
        }

        /// <summary>
        /// 增加列
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="columnNames">所有列的列名</param>
        /// <param name="columnDataTypes">所有列的数据类型</param>
        public void AddColumns(string tableName, List<string> columnNames, List<string> columnDataTypes = null)
        {
            if (columnDataTypes == null)
            {
                columnDataTypes = new List<string>();
                for (int i = 0; i < columnNames.Count; i++)
                {
                    columnDataTypes.Add("TEXT");
                }
            }
            StringBuilder commandText = new StringBuilder();

            BeginTransaction();
            for (int i = 0; i < columnNames.Count; i++)
            {
                commandText.Clear();
                commandText.Append(string.Format("ALTER TABLE {0} ADD ", tableName));
                commandText.Append(string.Format("{0} {1}", columnNames[i], columnDataTypes[i]));
                ExecuteNonQuery(commandText.ToString());
            }
            CommitTransaction();
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="sql"></param>
        public void ExecuteNonQuery(string sql)
        {
            try
            {
                sqliteCommand.CommandText = sql.Replace("'", "''");
                sqliteCommand.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                if (transactionOn) dbTransaction.Rollback();
                throw new ApplicationException("HAOest.Data.SQLite.ExecuteNonQuery：下列sql语句执行失败：\n" + sql, ex);
            }
        }

        /// <summary>
        /// 拼音排序功能
        /// </summary>
        [SQLiteFunction(FuncType = FunctionType.Collation, Name = "PinYin")]
        private class SQLiteCollationPinYin : SQLiteFunction
        {
            public override int Compare(string x, string y)
            {
                return string.Compare(x, y, false);
            }
        }

        /// <summary>
        /// 开启事务模式
        /// </summary>
        public void BeginTransaction()
        {
            dbTransaction = sqliteConnection.BeginTransaction();
            transactionOn = true;
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void CommitTransaction()
        {
            dbTransaction.Commit();
        }

        /// <summary>
        /// 关闭连接
        /// </summary>
        public void CloseConnection()
        {
            sqliteConnection.Close();
        }
    }
}
